  --CPU 속도 정렬 테이블
  drop table cpu_speed;
  
  create table cpu_speed(
  	no NUMBER, 
  	id varchar2(20) UNIQUE, 
  	constraint cpu_speed_fk foreign key(id) references product(id) on delete cascade
  );
  
  select * from cpu_speed order by no asc;
  select css.no, p.id, p.name from cpu_speed css, product p where css.ID = p.ID order by css.no asc;
  select count(no) from cpu_speed;

  delete from cpu_speed;
  
  --case 문법 테스트
  /*select case
  			when (select count(no) from cpu_speed)=0 then 'no'
  			else 'yes'
  			end as no
  		from cpu_speed;*/
  
  
  --cpu_speed 임시값 할당
  /*insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 3);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 10);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 14);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 18);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 17);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 12);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 11);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 375);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 77);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 32);
  insert into cpu_speed values((select NVL(max(no+1), 1) from cpu_speed), 15);*/
  

-- product 행 삭제시 cpu_speed 자동 정렬을 위한 트리거 생성
drop trigger delete_cpu;
	
  CREATE OR REPLACE TRIGGER delete_CPU
	AFTER DELETE ON product
	for each row
	WHEN(OLD.CATEGORY_ID='fc-2')
	DECLARE
		-- 자율 트랜잭션
		-- 부모 트랜잭션의 승인/롤백 여부에 상관없이 자신의 작업을 승인하거나 롤백할 수 있다.
		pragma autonomous_transaction;
		max_no number;
		del_no number;
	BEGIN
		if deleting then
			dbms_output.put_line('deleting');
			select max(no) into max_no from cpu_speed;
			select no into del_no from cpu_speed where id=:OLD.id;
			if max_no > del_no then
				update cpu_speed set no=no-1 where no > del_no;
				commit; --commit 구문 빠졌을시 trigger 오류 발생
			end if;
		end if;
	END;
	
-- product 행 추가시 cpu_speed 가장 마지막 no로 자동 등록을 수행하는 트리거 생성	
drop trigger insert_cpu;

  CREATE OR REPLACE TRIGGER insert_CPU
	AFTER INSERT ON product
	for each row
	WHEN(NEW.CATEGORY_ID='fc-2')
	BEGIN
		if inserting then
			dbms_output.put_line('inserting');
			insert into cpu_speed(no, id) values((select NVL(max(no+1), 1) from cpu_speed), :NEW.id);
		end if;
	END;
  
  
	
	
	
--insert_CPU 트리거 테스트--
  --cpu 속도별 검색을 위한 임시 상품등록
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i3 4360', 98700, 'AMD','i3 4360', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','A10 7850K', 98700, 'AMD','A10 7850K', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i5 3570', 98700, 'AMD','i5 3570', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i5 4460', 98700, 'AMD','i5 4460', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i3 4370', 98700, 'AMD','i3 4370', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','FX8300', 98700, 'AMD','FX8300', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i5 3470', 98700, 'AMD','i5 3470', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','FX6300', 98700, 'AMD','FX6300', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i5 4690K', 98700, 'AMD','i5 4690K', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i5 4690', 98700, 'AMD','i5 4690', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));
insert into product(id, file_name, name, price, brand, model_no, quantity, description, del, category_name, category_id) values('pn-'||product_id_seq.nextval, '','i5 4590', 98700, 'AMD','i5 4590', 100, 'AMD(소켓FM2+) / 쿼드 코어 / 쓰레드 4개 / 64비트 / 3.1GHz / 4MB / AMD R7 시리즈 / 720MHz', 'false', (select category_name from first_category where category_name='CPU'), (select category_id from first_category where category_name='CPU'));

select css.no, p.id, p.name from cpu_speed css, product p where css.ID = p.ID order by css.no asc;

--cpu 속도별 정렬
update cpu_speed set no=1 where ID='pn-99';
update cpu_speed set no=2 where ID='pn-102';
update cpu_speed set no=3 where ID='pn-103';
update cpu_speed set no=4 where ID='pn-104';
update cpu_speed set no=5 where ID='pn-96';
update cpu_speed set no=6 where ID='pn-97';
update cpu_speed set no=7 where ID='pn-100';
update cpu_speed set no=8 where ID='pn-101';
update cpu_speed set no=9 where ID='pn-98';
update cpu_speed set no=10 where ID='pn-95';
update cpu_speed set no=11 where ID='pn-94';

select * from product;
select * from product where category_name='CPU';

  
--delete_CPU 트리거 테스트--
delete from product where id='pn-83';
delete from product where id in (select id from product where CATEGORY_NAME='CPU');
--delete from cpu_speed where id='pn-84';
select * from product where CATEGORY_NAME='CPU';
select * from product;
select * from cpu_speed order by no asc;



--CPU 속도별 부품 조회
select * from product p, cpu_speed css where p.id=css.id(+) order by css.no asc;
select * from product p, cpu_speed css where p.id=css.id order by css.no asc;

--cpu_speed 테이블을 기준으로한 완제품 등록
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_1', '1번 PC', 75000, 32, 'pn-100', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_2', '2번 PC', 75000, 32, 'pn-97', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_3', '3번 PC', 75000, 32, 'pn-96', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_4', '4번 PC', 75000, 32, 'pn-102', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_5', '5번 PC', 75000, 32, 'pn-101', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_6', '6번 PC', 75000, 32, 'pn-104', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_7', '7번 PC', 75000, 32, 'pn-99', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_8', '8번 PC', 75000, 32, 'pn-96', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_9', '9번 PC', 75000, 32, 'pn-98', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_10', '10번 PC', 75000, 32, 'pn-99', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_11', '11번 PC', 75000, 32, 'pn-103', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_12', '12번 PC', 75000, 32, 'pn-94', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_13', '13번 PC', 75000, 32, 'pn-95', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_14', '14번 PC', 75000, 32, 'pn-96', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_15', '15번 PC', 75000, 32, 'pn-96', null);
insert into complete_pc(complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id) values('C_16', '16번 PC', 75000, 32, null, null);

--완제품 전체조회
select complete_id, complete_name, complete_price, complete_quantity, cpu_id, graphic_id from complete_pc;

--CPU 속도별 완제품 조회
select cp.complete_id, cp.complete_name, cp.complete_price, cp.complete_quantity, cp.cpu_id, cp.graphic_id, css.no from complete_pc cp, cpu_speed css where cp.cpu_id=css.id(+) order by css.no asc;


--CPU 속도별 완제품 조회 mapper 파일 작성
<select id="selectCompletePCByCPU" resultType="completePCResult">
   select	d.fileName, d.completeId, d.completeName, d.completePrice,
  				d.completeQuantity, d.mainboardName, d.cpuName,	d.coolerName,
  				d.memoryName, d.graphicName, d.hddName, d.ssdName, d.dvdName,
  				d.soundName, d.powerName, d.caseName, d.osName, d.cnt
  from	(<include refid="completePC-basic"/>) d, (select css.no no, p.id id, p.name name from cpu_speed css, product p where css.ID = p.ID) p 
  where d.cpuName=p.name(+) order by p.no ASC
  </select>